﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop;
using System.Diagnostics;
using log4net;
using log4net.Config;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel; 

namespace ExcelReportGenerator
{
    class ExcelApp
    {
        string TEMPLATE_FILE_EXCEL = @"C:\Work\SVN dedraak\A3M\EXCEL_APPS\Documenten\Template.xls";
        string REPORT_DIR_EXCEL = @"C:\Work\SVN dedraak\A3M\EXCEL_APPS\Rapporten\";
        string REPORT_DIR_PDF = @"C:\Work\SVN dedraak\A3M\EXCEL_APPS\Rapporten\";

        bool paramOpenAfterPublish = false;
        bool paramIncludeDocProps = false;
        bool paramIgnorePrintAreas = false;

        object paramFromPage = Type.Missing;
        object paramToPage = Type.Missing;

        // Create a logger
        ILog log = LogManager.GetLogger(typeof(Program));

        // Excel app
        Excel.Application excelAppTemplate;

        // Workbook
        Excel.Workbook wbTemplate;

        internal void start()
        {
            log.Debug("Open Excel Template");

            OpenExcelTemplate();

            try
            {
                Worksheet wsResponses = getWorksheetByName("responses");

                Excel.Range oRangeTarget = wsResponses.get_Range("A2");
                Excel.Range oRowTarget = oRangeTarget.EntireRow;

                int teller = 3;
                int max = 20;

                while (teller < max)
                {
                    Excel.Range oRangeSource = wsResponses.get_Range("A" + teller.ToString());
                    Excel.Range oRowSource = oRangeSource.EntireRow;
                    var oNaam = oRangeSource.get_Value(Missing.Value);
                    string excelFileName = oNaam;

                    if (excelFileName == null || excelFileName.Trim().Equals(""))
                    {
                        log.Debug("End reading responses end with row " + teller.ToString());
                        break;
                    }

                    log.Debug("Run name " + excelFileName);

                    oRowSource.Copy(oRowTarget);

                    excelAppTemplate.ActiveWorkbook.Sheets[1].Select(Type.Missing); //activates first sheet

                    Worksheet wsTemplate = getWorksheetByName("Template");

                    // Auto fit
                    for (int r = 115; r < 121; r++)
                        wsTemplate.get_Range("A" + r.ToString(), "A" + r.ToString()).EntireRow.AutoFit();

                    for (int r = 131; r < 142; r++)
                        wsTemplate.get_Range("A" + r.ToString(), "A" + r.ToString()).EntireRow.AutoFit();

                    // Run Macros
                    RunMacro(excelAppTemplate, new Object[] { "Blad1.RunMacros" });

                    string filePathXls = REPORT_DIR_EXCEL + excelFileName.Trim() + ".xls";
                    string filePathPdf = REPORT_DIR_PDF + excelFileName.Trim() + ".pdf";

                    // Delete XLS is exists
                    if (File.Exists(filePathXls))
                        File.Delete(filePathXls);

                    // Save XLS
                    wbTemplate.SaveAs(filePathXls);
                    log.Debug("Saved " + filePathXls);
                    
                    // Delete PDF is exists
                    if (File.Exists(filePathPdf))
                        File.Delete(filePathPdf);

                    // Hide sheets 
                    ((Excel.Worksheet)excelAppTemplate.ActiveWorkbook.Sheets[2]).Visible = Excel.XlSheetVisibility.xlSheetHidden;
                    ((Excel.Worksheet)excelAppTemplate.ActiveWorkbook.Sheets[3]).Visible = Excel.XlSheetVisibility.xlSheetHidden;
                    ((Excel.Worksheet)excelAppTemplate.ActiveWorkbook.Sheets[4]).Visible = Excel.XlSheetVisibility.xlSheetHidden;

                    wbTemplate.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, filePathPdf, XlFixedFormatQuality.xlQualityStandard, paramIncludeDocProps,
                            paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish, Type.Missing);
                    log.Debug("Saved " + filePathPdf);

                    teller++;
                }
            }
            catch(Exception e)
            {
                log.Error("Failed to generate excel file.",e);
            }
            finally
            {
                Close();
            }
        }

        private void OpenExcelTemplate()
        {
            
            excelAppTemplate = new Excel.Application();
            object paramMissing = Type.Missing;

            // Open the source workbook.
            wbTemplate = excelAppTemplate.Workbooks.Open(TEMPLATE_FILE_EXCEL, paramMissing, paramMissing, paramMissing,
                paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,
                paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
        }

        private Worksheet getWorksheetByName(string name)
        {
            foreach (Worksheet sheet in wbTemplate.Worksheets)
            {
                if (sheet.Name == name)
                {
                    return sheet;
                }
            }
            return null;
        }

        private void Close()
        {   
            // Close the workbook object.
            if (wbTemplate != null)
            {
                wbTemplate.Close(false);
                wbTemplate = null;
            }

            // Quit Excel and release the ApplicationClass object.
            if (excelAppTemplate != null)
            {
                excelAppTemplate.Quit();
                excelAppTemplate = null;
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        public void RunMacro(object oApp, object[] oRunArgs)
        {
            lock (this)
            {
                oApp.GetType().InvokeMember("Run",
                 System.Reflection.BindingFlags.Default |
                 System.Reflection.BindingFlags.InvokeMethod,
                 null, oApp, oRunArgs);
            }
        }
       
    }

}
